#!  /usr/bin/env pytreon
#ecoding=utf
from yuncaiji.utils.db_utils import DBUtils
from pymysql import cursors
import traceback
import redis
from ..setting import *
''
rds = redis.StrictRedis(host=REDIS_IP, port=REDIS_PORT,password=REDIS_PASSWORD,decode_responses=True)
class TemplateUrlDao():
    #查询没执行成功的，并且请求没有成功的（也不能是正在运行的）
    @staticmethod
    def get_no_spider_urls(read_url_max):
        cursor='';
        try:
            sql = "select a.* from t_template_url a where a.status !=5 and (ISNULL(a.html_request_status) or a.html_request_status !=5) and (ISNULL(a.html_request_status) or a.html_request_status !=1) "
            conn = DBUtils.get_connection_pool().connection()
            cursor= conn.cursor(cursor=cursors.DictCursor)
            cursor.execute(sql)
            rss = cursor.fetchall()
            return rss
        except Exception as e:
            print('error where execute fetch....rollback...')
            traceback.print_exc()


    #查询java解析html没成功的，并且请求成功的
    @staticmethod
    def get_template_urls():
        cursor='';
        try:
            sql = "select a.* from t_template_url a where a.status !=5 and a.status !=1 and a.html_request_status =5"
            conn = DBUtils.get_connection_pool().connection()
            cursor= conn.cursor(cursor=cursors.DictCursor)
            cursor.execute(sql)
            rss = cursor.fetchall()
            return rss
        except Exception as e:
            print('error where execute fetch....rollback...')
            traceback.print_exc()




    @staticmethod
    def get_no_spider_urls_and_update(read_url_max):
        #drop PROCEDURE `find_and_update_template_urls`;
        # CREATE  PROCEDURE `find_and_update_template_urls`(IN max_num BIGINT)
        # begin
        # SET @url_ids := null;
        # update t_template_url a set a.html_request_status=1 where a.id in(
        #     select t.id from(
        #     select a.id from t_template_url a
        # where a.status !=5
        #       and (ISNULL(a.html_request_status) or a.html_request_status !=5)
        #       and (ISNULL(a.html_request_status) or a.html_request_status !=1)
        # LIMIT 0,10
        # )t where 1=1 and ( SELECT @url_ids := CONCAT_WS(',', t.id, @url_ids) )
        # );
        # select @url_ids;
        # end;
        url_ids=TemplateUrlDao.get_url_ids(read_url_max);
        if not url_ids:
            return ;
        else:
            try:
                sql = "select a.* from t_template_url a where a.id in ("+str(url_ids)+")"
                print('执行SQL:'+sql)
                conn = DBUtils.get_connection_pool().connection()
                cursor= conn.cursor(cursor=cursors.DictCursor)
                cursor.execute(sql)
                rss = cursor.fetchall()
                return rss
            except Exception as e:
                print('error where execute fetch....rollback...')
                traceback.print_exc()
    @staticmethod
    def get_url_ids(read_url_max):
        try:
            conn = DBUtils.get_connection_pool().connection()
            cursor= conn.cursor(cursor=cursors.DictCursor)
            cursor.callproc('find_and_update_template_urls',(read_url_max,))
            data = cursor.fetchone()
            print('存储过程返回结果:'+str(data))
            conn.commit();
            if data['@url_ids']:
                return str(data['@url_ids'],'utf-8')
            return '';
        except Exception as e:
            print('error where execute TemplateUrlDao.get_url_ids....rollback...')
            traceback.print_exc()
    #请求完成
    @staticmethod
    def status_update_one(obj):
            try:
                sql = "update t_template_url  set html_request_status = "+str(obj['html_request_status'])+" where id ="+str(obj['id'])
                print('执行更新操作,SQL:'+sql)
                conn = DBUtils.get_connection_pool().connection()
                cursor=conn.cursor(cursor=cursors.DictCursor)
                cursor.execute(sql)
                conn.commit()
            except Exception as e:
                print('error where execute update.....rollback..')
                traceback.print_exc()
    @staticmethod
    def status_update_one2(obj):
        try:
            sql = "update t_template_url  set status = "+str(obj['status'])+" where id ="+str(obj['id'])
            print('执行更新操作,SQL:'+sql)
            conn = DBUtils.get_connection_pool().connection()
            cursor=conn.cursor(cursor=cursors.DictCursor)
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print('error where execute update.....rollback..')
            traceback.print_exc()
    @staticmethod
    def page_update_one(obj):
        try:
            conn = DBUtils.get_connection_pool().connection()
            sql = "update t_template_url  set page = "+str(obj['page'])+" where id ="+str(obj['id'])
            print('执行更新操作,SQL:'+sql)
            cursor=conn.cursor(cursor=cursors.DictCursor)
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print('error where execute update.....rollback..')
            traceback.print_exc()
    @staticmethod
    def status_html_update_all(objs):
        for obj in objs:
            try:
                conn = DBUtils.get_connection_pool().connection()
                sql = 'update t_template_url  set html_request_status = %s ,html = %s  where id =%s'
                print('执行更新操作,SQL:'+sql)
                cursor=conn.cursor(cursor=cursors.DictCursor)
                cursor.execute(sql,(obj['html_request_status'],obj['html'],obj['id']))
                conn.commit()
            except Exception as e:
                print('error where execute update all.....rollback..')
                traceback.print_exc()
                obj['html_request_status']=6
                TemplateUrlDao.status_update_one(obj)
    @staticmethod
    def status_html_update_one(obj):
            try:
                conn = DBUtils.get_connection_pool().connection()
                sql = 'update t_template_url  set html_request_status = %s ,html = %s  where id =%s'
                print('执行更新操作,SQL:'+sql)
                cursor=conn.cursor(cursor=cursors.DictCursor)
                cursor.execute(sql,(obj['html_request_status'],obj['html'],obj['id']))
                conn.commit()
            except Exception as e:
                print('error where execute update one.....rollback..')
                traceback.print_exc()
                obj['html_request_status']=6
                TemplateUrlDao.status_update_one(obj)